package com.oceansoft.core.jxkh.dao.impl;

import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.domain.SysUser;
import com.oceansoft.core.jxkh.dao.IPersonnelDao;
import com.oceansoft.core.jxkh.domain.Personnel;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by OS on 2017/12/5.
 */

@Repository
public class PersonnelDaoImpl implements IPersonnelDao{

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Override
    public Pagination findAllPersonnel(HashMap<String,String> map) {
        int offset = Integer.parseInt(map.get("offset").toString());
        int limit = Integer.parseInt(map.get("limit").toString());
        String sql = "select * from t_sys_user order by dbid";
        String count_sql = PageUtil.getCountSQL(sql.toString());
        int _total = jdbcTemplate.queryForInt(count_sql);
        String page_sql = PageUtil.getPageSQL(sql.toString(), offset,limit);
        List < SysUser > list = jdbcTemplate.query(page_sql, new RowMapper<SysUser>() {
            @Override
            public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
                SysUser sysUser = new SysUser();
                sysUser.setDbid(rs.getInt("dbid"));
                sysUser.setName(rs.getString("name"));
                sysUser.setPoliceNum(rs.getString("policeNum"));
                sysUser.setTel_mobile(rs.getString("tel_mobile"));
                sysUser.setDutyName(rs.getString("dutyName"));
                sysUser.setDutyLevel(rs.getString("dutyLevel"));
                sysUser.setDeptManager(rs.getInt("deptManager"));
                sysUser.setDeptId(rs.getString("deptId"));
                sysUser.setActiveFlag(rs.getString("activeFlag"));
                sysUser.setJoinCheck(rs.getInt("joinCheck"));
                return sysUser;
            }
        });
        return new Pagination(list,_total,limit, (offset/limit)+1);
    }

    @Override
    public void editPersonnelInfo(SysUser sysUser) {
        String sql = "update t_sys_user set policeNum = ? , dutyName = ? , dutyLevel  = ? , tel_mobile = ? where dbid = ?";
        jdbcTemplate.update(sql,sysUser.getPoliceNum(),sysUser.getDutyName(),sysUser.getDutyLevel(),sysUser.getTel_mobile(),sysUser.getDbid());
    }

    @Override
    public SysUser findPersonnelInfoByDbid(int dbid) {
        String sql = "select * from T_SYS_USER where dbid = ?";
        SysUser sysUser = jdbcTemplate.queryForObject(sql,new Object[]{dbid},new RowMapper<SysUser>() {

            @Override
            public SysUser mapRow(ResultSet rs, int args) throws SQLException {
                SysUser sysUser = new SysUser();
                sysUser.setDbid(rs.getInt("dbid"));
                sysUser.setName(rs.getString("name"));
                sysUser.setPoliceNum(rs.getString("policeNum"));
                sysUser.setTel_mobile(rs.getString("tel_mobile"));
                sysUser.setDutyName(rs.getString("dutyName"));
                sysUser.setDutyLevel(rs.getString("dutyLevel"));
                sysUser.setDeptManager(rs.getInt("deptManager"));
                sysUser.setDeptId(rs.getString("deptId"));
                sysUser.setActiveFlag(rs.getString("activeFlag"));
                sysUser.setJoinCheck(rs.getInt("joinCheck"));
                return sysUser;
            }
        });
        return sysUser;
    }

    @Override
    public void deletePersonnelInfoByDbid(int dbid) {
        String sql = "delete from t_sys_user where dbid = ?";
        jdbcTemplate.update(sql,dbid);
    }

    @Override
    public Pagination findPersonnelInfoByDeptId(HashMap<String,String> map) {
        int offset = Integer.parseInt(map.get("offset").toString());
        int limit = Integer.parseInt(map.get("limit").toString());
        String sql = "select * from t_sys_user where deptId = ?";
        String count_sql = PageUtil.getCountSQL(sql.toString());
        int _total = jdbcTemplate.queryForInt(count_sql,map.get("deptId"));
        String page_sql = PageUtil.getPageSQL(sql.toString(), offset,limit);
        List<SysUser> list = jdbcTemplate.query(page_sql,new Object[]{map.get("deptId")},new RowMapper<SysUser>() {

            @Override
            public SysUser mapRow(ResultSet rs, int args) throws SQLException {
                SysUser sysUser = new SysUser();
                sysUser.setDbid(rs.getInt("dbid"));
                sysUser.setName(rs.getString("name"));
                sysUser.setPoliceNum(rs.getString("policeNum"));
                sysUser.setTel_mobile(rs.getString("tel_mobile"));
                sysUser.setDutyName(rs.getString("dutyName"));
                sysUser.setDutyLevel(rs.getString("dutyLevel"));
                sysUser.setDeptManager(rs.getInt("deptManager"));
                sysUser.setDeptId(rs.getString("deptId"));
                sysUser.setActiveFlag(rs.getString("activeFlag"));
                sysUser.setJoinCheck(rs.getInt("joinCheck"));
                return sysUser;
            }
        });
        return new Pagination(list,_total,limit, (offset/limit)+1);
    }

    @Override
    public void editDeptManagerByDbidToYes(int dbid) {
        String sql="update t_sys_user set deptManager = 1 where dbid = ?";
        jdbcTemplate.update(sql,new Object[]{dbid});
    }

    @Override
    public void editDeptManagerToNo(int deptId) {
        String sql = "update t_sys_user set deptManager = 0 where deptId = ? and deptManager = 1";
        jdbcTemplate.update(sql,new Object[]{deptId});
    }

    @Override
    public void editJoinCheck(int dbid,int i) {
        String sql = "update t_sys_user set joinCheck = ? where dbid = ?";
        jdbcTemplate.update(sql,new Object[]{i,dbid});
    }

    @Override
    public void editActiveFlag(int dbid,int i) {
        String sql = "update t_sys_user set activeFlag = ? where dbid = ?";
        jdbcTemplate.update(sql,new Object[]{i,dbid});
    }

    @Override
    public int findJoinCheck(int dbid) {
        String sql = "select joinCheck from t_sys_user where dbid = ?";
        return jdbcTemplate.queryForInt(sql,new Object[]{dbid});
    }

    @Override
    public int findActiveFlag(int dbid) {
        String sql = "select ActiveFlag from t_sys_user where dbid = ?";
        return jdbcTemplate.queryForInt(sql,new Object[]{dbid});
    }

}
